import xlrd
from xlutils.copy import copy


def read_data():
    wb = xlrd.open_workbook('../02.py_excelFile/data01.xls')
    # wb = xlrd.open_workbook('data01.xls')
    sh = wb.sheet_by_index(0)
    fen_type = {}  # {A:100, B:300}  该容器用来记录 厂商分类的价格
    count_price = []  # [1,2,3,4,5,6]  该容器用来记录 总的价格

    for r in range(sh.nrows):
        count = sh.cell_value(r, 3) * sh.cell_value(r, 4)
        count_price.append(count)
        key = sh.cell_value(r, 0)
        if fen_type.get(key):
            fen_type[key] += count
        else:
            fen_type[key] = count

    return fen_type, count_price  # 各分类的总价值，每个单品的总价值


def save(fen, count):
    wb = xlrd.open_workbook('../02.py_excelFile/data01.xls')
    # wb = xlrd.open_workbook('data01.xls')
    sh_t = wb.sheet_by_index(0)
    wb2 = copy(wb)
    sh = wb2.get_sheet(0)
    for r in range(sh_t.nrows):
        sh.write(r, sh_t.ncols, count[r])  # 写具体的数据

    sh2 = wb2.add_sheet('汇总数据')
    for i, key in enumerate(fen.keys()):
        sh2.write(i, 0, key)  # 第 i 行，第 0 列，具体的key
        sh2.write(i, 1, fen.get(key))  # 第 i 行，第 0 列，具体的数据

    wb2.save('../02.py_excelFile/data01-汇总数据.xlsx')


if __name__ == '__main__':
    fen, count = read_data()
    save(fen, count)
